
/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package facade;

import delta.emailer.EmailerLocal;
import entity.Orders;
import entity.Product;
import entity.ProductList;
import entity.Users;
import entity.Warehouse;
import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.annotation.PostConstruct;
import javax.annotation.Resource;
import javax.ejb.EJB;
import javax.ejb.SessionContext;
import javax.ejb.Stateless;
import javax.ejb.TransactionAttribute;
import javax.ejb.TransactionAttributeType;
import javax.persistence.EntityManager;
import javax.persistence.NoResultException;
import javax.persistence.NonUniqueResultException;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.sql.DataSource;
import javax.transaction.UserTransaction;
import oracle.jdbc.OracleTypes;

/**
 *
 * @author Svetlana Shikhutskaya, Kate, Ivchenko Oleg
 */
@Stateless
//@TransactionManagement(TransactionManagementType.BEAN)
//@TransactionAttribute(TransactionAttributeType.SUPPORTS)
public class OrdersFacade extends AbstractFacade<Orders> implements OrdersFacadeLocal {

    @EJB
    private EmailerLocal emailer;
    @EJB
    private UsersFacadeLocal usersFacade;
    @EJB
    private WarehouseFacadeLocal warehouseFacade;
    @Resource
    protected SessionContext ctx;
    @EJB
    private ProductListFacadeLocal productListFacade;
    @PersistenceContext(unitName = "Delta-ejbPU")
    private EntityManager em;
    @Resource
    private UserTransaction tx;
    @Resource(mappedName = "jdbc/delta")
    private DataSource ds;
    private List<String> statuses;

    @Override
    protected EntityManager getEntityManager() {
        return em;
    }

    public OrdersFacade() {
        super(Orders.class);
    }

    /**
     * @author Andriy Polishchuk
     * @param idOrder
     * @return
     */
    @Override
    public int setDelivered(int idOrder) {
        Orders order = find(idOrder);
        if (order != null) {
            if (order.getOrderStatus().equalsIgnoreCase("shipped")) {
                order.setDeliveryDate(new java.util.Date());
                order.setOrderStatus("delivered");
                edit(order);
                emailer.notifyCustomer(order); //KATE,Don't forget to UNCOMMET!!!
                return 1;
            } else {
                return 0;
            }
        } else {
            return 0;
        }
    }

    /**
     * @author Andriy Polishchuk
     * @param idOrder
     * @return
     */
    @Override
    public int setShipped(int idOrder) {
        Orders order = find(idOrder);
        if (order != null) {
            if (order.getOrderStatus().equalsIgnoreCase("queued")) {
                order.setDeliveryDate(new java.util.Date());
                order.setOrderStatus("shipped");
                edit(order);
                emailer.notifyCourier(order); //KATE,Don't forget to UNCOMMET!!!
                emailer.notifyCustomer(order); //KATE,Don't forget to UNCOMMET!!!
                return 1;
            } else {
                return 0;
            }
        } else {
            return 0;
        }
    }

    /**
     * @author Andriy Polishchuk
     * @param idOrder
     * @return
     */
    @Override
    public int cancelOrder(int idOrder) {
        Orders order = find(idOrder);
        if (order != null) {
            if (order.getOrderStatus().equalsIgnoreCase("queued")) {
                order.setDeliveryDate(new java.util.Date());
                order.setOrderStatus("canceled");
                edit(order);
                emailer.notifyCustomer(order); //KATE,Don't forget to UNCOMMET!!!
                return 1;
            } else {
                return 0;
            }
        } else {
            return 0;
        }
    }

    /**
     * @author Andriy Polishchuk
     * @param range
     * @param status
     * @param sortBy
     * @return
     */
    @Override
    public List<Orders> findRangeSortBy(int pageNum, String status, String orderBy, String sortType) {
        Connection con = null;
        ResultSet rs = null;
        CallableStatement cstmt = null;
        List<Orders> lo = new ArrayList();
        try {
            con = ds.getConnection();
            //call stored procedure
            cstmt = con.prepareCall("{call getOrdersByStatus(?,?,?,?,?)}");
            //set parameters
            if (sortType.equalsIgnoreCase("asc")) {
                cstmt.setInt("ascDesc", 1);
            } else {
                cstmt.setInt("ascDesc", 2);
            }
            if (orderBy.equalsIgnoreCase("idOrder")) {
                cstmt.setInt("orderBy", 1);
            } else if (orderBy.equalsIgnoreCase("orderDate")) {
                cstmt.setInt("orderBy", 2);
            } else if (orderBy.equalsIgnoreCase("idWarehouse")) {
                cstmt.setInt("orderBy", 3);
            } else if (orderBy.equalsIgnoreCase("shippingPrice")) {
                cstmt.setInt("orderBy", 4);
            } else if (orderBy.equalsIgnoreCase("productPrice")) {
                cstmt.setInt("orderBy", 5);
            } else if (orderBy.equalsIgnoreCase("address")) {
                cstmt.setInt("orderBy", 6);
            }
            cstmt.setInt("page_num", pageNum);
            cstmt.setString("status", status);
            cstmt.registerOutParameter("p_refcur", OracleTypes.CURSOR);
            cstmt.executeUpdate();
            //get ResultSet
            rs = (ResultSet) cstmt.getObject("p_refcur");
            //make List of Orders 

            while (rs.next()) {
                Orders order = new Orders(rs.getInt("idOrder"),
                        rs.getDate("orderDate"),
                        rs.getString("orderStatus"),
                        rs.getBigDecimal("shippingPrice"),
                        rs.getBigDecimal("productPrice"),
                        rs.getString("email"),
                        rs.getString("orderAddress"),
                        rs.getInt("trackingNumber"));
                Warehouse wh = new Warehouse();
                wh.setName(rs.getString("warehouseName"));
                order.setDeliveryDate(rs.getDate("deliveryDate"));
                order.setIdWarehouse(wh);
                lo.add(order);
            }
        } catch (SQLException ex) {
            Logger.getLogger(OrdersFacade.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                //close Recordset,CallableStatement, Connection
                if (rs != null) {
                    rs.close();
                }
                if (cstmt != null) {
                    cstmt.close();
                }
                if (con != null) {
                    con.close();
                }
                return lo;
            } catch (SQLException ex) {
                Logger.getLogger(CategoryFacade.class.getName()).log(Level.SEVERE, null, ex);
                return lo;
            }
        }
    }

    @Override
    public int count(String status) {
        return Integer.parseInt(getEntityManager().createNamedQuery("Orders.countByStatus").setParameter("orderStatus", status).getSingleResult().toString());

    }

    @Deprecated
    @TransactionAttribute(TransactionAttributeType.MANDATORY)
    public void create(Orders order) {

        super.create(order);
        System.out.println("kate1043.order created");
    }

    @TransactionAttribute(TransactionAttributeType.REQUIRES_NEW)
    @Override
    public void create(Orders order, HashMap<Product, Integer> productList) {

        try {
            em.persist(order);

            for (Product product : productList.keySet()) {
                ProductList prodList = new ProductList(
                        product.getId_product(),
                        order.getIdOrder(),
                        productList.get(product));
                productListFacade.create(prodList);
            }
            em.flush();
            em.refresh(order);
            emailer.notifyWarehouseEmp(order);  //KATE,Don't forget to UNCOMMET!!!
            emailer.notifyCustomer(order); //KATE,Don't forget to UNCOMMET!!!
        } catch (Exception e) {
            System.out.println("Exception while creating order:" + e.getMessage());
            ctx.setRollbackOnly();
            System.out.println(ctx.getRollbackOnly());
        }

    }

    //Svetlana Shikhutskaya
    @Override
    public List<Orders> findRangeSortBy(int pageNumber, Users user, String sortBy) {
        Connection conn = null;
        ResultSet rs = null;
        CallableStatement cstmt = null;
        List<Orders> orderList = new Vector<Orders>();
        try {
            conn = ds.getConnection();
            cstmt = conn.prepareCall("{call getUserOrders(?,?,?,?)}");
            cstmt.setInt("page_num", pageNumber);
            if (sortBy.equals("Date")) {
                cstmt.setInt("orderBy", 1);
            } else if (sortBy.equals("Sum")) {
                cstmt.setInt("orderBy", 2);
            } else if (sortBy.equals("Number")) {
                cstmt.setInt("orderBy", 3);
            }
            cstmt.setInt("userId", user.getIdUser());
            cstmt.registerOutParameter("p_refcur", OracleTypes.CURSOR);
            cstmt.executeUpdate();
            rs = (ResultSet) cstmt.getObject("p_refcur");
            while (rs.next()) {
                Orders order = new Orders();
                order.setOrderDate(rs.getDate("orderDate"));
                order.setTrackingNumber(rs.getInt("trackingNumber"));
                order.setOrderStatus(rs.getString("orderStatus"));
                Warehouse wr = warehouseFacade.find(rs.getInt("idWarehouse"));
                order.setIdWarehouse(wr);
                order.setShippingPrice(rs.getBigDecimal("shippingPrice"));
                order.setDeliveryDate(rs.getDate("deliveryDate"));
                order.setProductPrice(rs.getBigDecimal("productPrice"));
                order.setEMail(rs.getString("email"));
                order.setAddress(rs.getString("address"));
                orderList.add(order);
            }
        } catch (SQLException ex) {
            Logger.getLogger(OrdersFacade.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (cstmt != null) {
                    cstmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(OrdersFacade.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return orderList;
    }

    //Svetlana Shikhutskaya
    @Override
    public List<Orders> findRangeByKey(int FirstResult, int Maxresults, Users idUser, Integer key) {
        Query query = null;
        query = getEntityManager().createNamedQuery("Orders.findByIdUserAndKey");
        if (query != null) {
            query.setParameter("trackingNumber", "%" + key + "%");
            query.setParameter("idUser", idUser);
            Vector<Orders> orders = (Vector<Orders>) query.setFirstResult(FirstResult).setMaxResults(Maxresults).getResultList();
            return orders;
        } else {
            return null;
        }
    }

    /**
     * ***************************************************************
     * <FONT COLOR = "RED"> <B> METHODS FOR REPORTING </B> </FONT>
     * ***************************************************************
     */
    /**
     * Ivchenko Oleg Find list of orders by concrete status
     *
     * @param status
     * @return
     *
     */
    @Override
    public List<Orders> findByStatus(String status) {
        Query query = getEntityManager().createNamedQuery("Orders.findByStatus");
        if (query != null) {
            query.setParameter("orderStatus", "%" + status + "%");
            return query.getResultList();
        } else {
            return null;
        }
    }

    /**
     * Ivchenko Oleg Find list of orders, which price lies in limits from
     * <code> minPrice </code> to
     * <code> maxPrice </code>
     *
     * @param minPrice
     * @param maxPrice
     * @return
     */
    @Override
    public List<Orders> findByPrice(BigDecimal minPrice, BigDecimal maxPrice) {
        if (minPrice.compareTo(maxPrice) >= 0) {
            return null;
        } else {
            Query query = getEntityManager().createNamedQuery("Orders.findByPriceInterval");
            if (query != null) {
                query.setParameter("minPrice", minPrice).setParameter("maxPrice", maxPrice);
                return query.getResultList();
            } else {
                return null;
            }
        }
    }

    /**
     * Ivchenko Oleg Method using for building report table <b>(1st column)</b>
     *
     * @return list of all orders statuses
     */
    @Override
    public List<String> getListStatuses() {
        return statuses;
    }

    //Ivchenko Oleg
    @Override
    public List<Integer> getListQuantities() {
        List<Integer> quantities = new ArrayList<Integer>();
        //Query query = getEntityManager().createNamedQuery("Orders.findByOrderStatus");
        Query query = getEntityManager().createNamedQuery("Orders.countByStatus");
        for (String status : statuses) {
            query.setParameter("orderStatus", status);
            if (query != null) {
                Long q = (Long) query.getSingleResult();
                quantities.add(q.intValue());
                //quantities.add(query.getResultList().size());
            }
        }
        if (!quantities.isEmpty()) {
            return quantities;
        } else {
            return null;
        }
    }

    //Ivchenko Oleg
    @Override
    public List<BigDecimal> getListPrices() {
        List<BigDecimal> prices = new ArrayList<BigDecimal>();
        for (String status : statuses) {
            List<Orders> orders = findByStatus(status);
            //double price = 0.0;
            BigDecimal price = new BigDecimal(0);
            for (Orders order : orders) {
                //price += order.getProductPrice().doubleValue() + order.getShippingPrice().doubleValue();
                price = price.add(order.getProductPrice()).add(order.getShippingPrice());
            }
            //prices.add(new BigDecimal(price));
            prices.add(price);
        }
        if (!prices.isEmpty()) {
            return prices;
        } else {
            return null;
        }
    }

//    /**
//     * Intermediate method for checking algorithm of getListPrices()
//     *
//     * @return
//     * @deprecated
//     */
//    @Deprecated
//    @Override
//    public List<String> getOrdersNamesByStatus() {
//        List<String> names = new ArrayList<String>();
//        for (String status : statuses) {
//            List<Orders> orders = findByStatus(status);
//            String name = "";
//            for (Orders order : orders) {
//                name += order.getIdOrder() + "; ";
//            }
//            names.add(name);
//        }
//        if (!names.isEmpty()) {
//            return names;
//        } else {
//            names.add("a");
//            names.add("a");
//            return names;
//        }
//    }
    //Ivchenko Oleg
    private List<Orders> findByStatusAndWarehousesList(String status, Integer idWarehouse) {
        List<Orders> orders = new ArrayList<Orders>();
        Query query = getEntityManager().createNamedQuery("Orders.findByStatusAndWarehouse");
        query.setParameter("orderStatus", status);
        query.setParameter("idWarehouse", idWarehouse.longValue());
        if (query != null) {
            orders.addAll(query.getResultList());
        }
        if (!orders.isEmpty()) {
            return orders;
        } else {
            return null;
        }
    }

    //Ivchenko Oleg
    private List<Orders> findByStatusAndUsersList(String status, Integer idUser) {
        List<Orders> orders = new ArrayList<Orders>();
        Query query = getEntityManager().createNamedQuery("Orders.findByStatusAndUser");
        query.setParameter("orderStatus", status);
        query.setParameter("idUser", idUser.longValue());
        if (query != null) {
            orders.addAll(query.getResultList());
        }
        if (!orders.isEmpty()) {
            return orders;
        } else {
            return null;
        }
    }

    //Ivchenko Oleg
    @Override
    public List<Orders> findByStatusUniversal(String status, Integer idUser, Integer idWarehouse) {
        Query query = null;
        if (status != null && !status.equals("all") && !status.equals("")) {
            if (idUser != 0 && idWarehouse != 0) {
                query = getEntityManager().createNamedQuery("Orders.findByStatusAndUserAndWarehouse");
                query.setParameter("orderStatus", status);
                query.setParameter("idUser", usersFacade.find(idUser));
                query.setParameter("idWarehouse", warehouseFacade.find(idWarehouse));
            } else if (idUser != 0) {
                query = getEntityManager().createNamedQuery("Orders.findByStatusAndUser");
                query.setParameter("orderStatus", status);
                query.setParameter("idUser", usersFacade.find(idUser));
            } else if (idWarehouse != 0) {
                query = getEntityManager().createNamedQuery("Orders.findByStatusAndWarehouse");
                query.setParameter("orderStatus", status);
                query.setParameter("idWarehouse", warehouseFacade.find(idWarehouse));
            } else {
                query = getEntityManager().createNamedQuery("Orders.findByStatus");
                query.setParameter("orderStatus", status);
            }
        } else {
            if (idUser != 0 && idWarehouse != 0) {
                query = getEntityManager().createNamedQuery("Orders.findByUserAndWarehouse");
                query.setParameter("idUser", usersFacade.find(idUser));
                query.setParameter("idWarehouse", warehouseFacade.find(idWarehouse));
            } else if (idUser != 0) {
                query = getEntityManager().createNamedQuery("Orders.findByIdUser");
                query.setParameter("idUser", usersFacade.find(idUser));
            } else if (idWarehouse != 0) {
                query = getEntityManager().createNamedQuery("Orders.findByIdWarehouse");
                query.setParameter("idWarehouse", warehouseFacade.find(idWarehouse));
            } else {
                query = getEntityManager().createNamedQuery("Orders.findAll");
            }
        }
        //ordOutput(query.getResultList());
        return query.getResultList();
    }

    //Ivchenko Oleg
    @Override
    public List<Orders> findByStatusUniversalPlusDate(String status, Integer idUser, Integer idWarehouse, Date startDate, Date endDate) {
        Query query = null;
        if (startDate == null && endDate == null) {
            if (status != null && !status.equals("all") && !status.equals("")) {
                if (idUser != 0 && idWarehouse != 0) {
                    query = getEntityManager().createNamedQuery("Orders.findByStatusAndUserAndWarehouse");
                    query.setParameter("orderStatus", status);
                    query.setParameter("idUser", usersFacade.find(idUser));
                    query.setParameter("idWarehouse", warehouseFacade.find(idWarehouse));
                } else if (idUser != 0) {
                    query = getEntityManager().createNamedQuery("Orders.findByStatusAndUser");
                    query.setParameter("orderStatus", status);
                    query.setParameter("idUser", usersFacade.find(idUser));
                } else if (idWarehouse != 0) {
                    query = getEntityManager().createNamedQuery("Orders.findByStatusAndWarehouse");
                    query.setParameter("orderStatus", status);
                    query.setParameter("idWarehouse", warehouseFacade.find(idWarehouse));
                } else {
                    query = getEntityManager().createNamedQuery("Orders.findByStatus");
                    query.setParameter("orderStatus", status);
                }
            } else {
                if (idUser != 0 && idWarehouse != 0) {
                    query = getEntityManager().createNamedQuery("Orders.findByUserAndWarehouse");
                    query.setParameter("idUser", usersFacade.find(idUser));
                    query.setParameter("idWarehouse", warehouseFacade.find(idWarehouse));
                } else if (idUser != 0) {
                    query = getEntityManager().createNamedQuery("Orders.findByIdUser");
                    query.setParameter("idUser", usersFacade.find(idUser));
                } else if (idWarehouse != 0) {
                    query = getEntityManager().createNamedQuery("Orders.findByIdWarehouse");
                    query.setParameter("idWarehouse", warehouseFacade.find(idWarehouse));
                } else {
                    query = getEntityManager().createNamedQuery("Orders.findAll");
                }
            }
        } else {
            java.sql.Date startDateSQL = null,
                    endDateSQL = null;
            if (startDate != null) {
                startDateSQL = new java.sql.Date(startDate.getTime());
            }
            if (endDateSQL != null) {
                endDateSQL = new java.sql.Date(endDate.getTime());
            }
            if (status != null && !status.equals("all") && !status.equals("")) {
                if (idUser != 0 && idWarehouse != 0) {
                    query = getEntityManager().createNamedQuery("Orders.findByStatusAndUserAndWarehouseAndDateInterv");
                    query.setParameter("idWarehouse", warehouseFacade.find(idWarehouse));
                    query.setParameter("idUser", usersFacade.find(idUser));
                    query.setParameter("startDate", startDate);
                    query.setParameter("endDate", endDate);
                    query.setParameter("orderStatus", status);
                } else if (idUser != 0) {
                    query = getEntityManager().createNamedQuery("Orders.findByStatusAndUserAndDateInterv");
                    query.setParameter("idUser", usersFacade.find(idUser));
                    query.setParameter("startDate", startDate);
                    query.setParameter("endDate", endDate);
                    query.setParameter("orderStatus", status);
                } else if (idWarehouse != 0) {
                    query = getEntityManager().createNamedQuery("Orders.findByStatusAndWarehouseAndDateInterv");
                    query.setParameter("idWarehouse", warehouseFacade.find(idWarehouse));
                    query.setParameter("startDate", startDate);
                    query.setParameter("endDate", endDate);
                    query.setParameter("orderStatus", status);
                } else {
                    query = getEntityManager().createNamedQuery("Orders.findByStatusAndDateInterv");
                    query.setParameter("startDate", startDate);
                    query.setParameter("endDate", endDate);
                    query.setParameter("orderStatus", status);
                }
            } else {
                if (idUser != 0 && idWarehouse != 0) {
                    query = getEntityManager().createNamedQuery("Orders.findByUserAndWarehouseAndDateInterv");
                    query.setParameter("idWarehouse", warehouseFacade.find(idWarehouse));
                    query.setParameter("idUser", usersFacade.find(idUser));
                    query.setParameter("startDate", startDate);
                    query.setParameter("endDate", endDate);
                } else if (idUser != 0) {
                    query = getEntityManager().createNamedQuery("Orders.findByUserAndDateInterv");
                    query.setParameter("idUser", usersFacade.find(idUser));
                    query.setParameter("startDate", startDate);
                    query.setParameter("endDate", endDate);
                } else if (idWarehouse != 0) {
                    query = getEntityManager().createNamedQuery("Orders.findByWarehouseAndDateInterv");
                    query.setParameter("idWarehouse", warehouseFacade.find(idWarehouse));
                    query.setParameter("startDate", startDate);
                    query.setParameter("endDate", endDate);
                } else {
                    query = getEntityManager().createNamedQuery("Orders.findByDateInterv");
                    query.setParameter("startDate", startDate);
                    query.setParameter("endDate", endDate);
                }
            }
        }
        //ordOutput(query.getResultList());
        return query.getResultList();
    }

    /**
     * Ivchenko Oleg
     *
     * @param status - concrete status
     * @param idUser - concrete idUser
     * @param idWarehouse - concrete idWarehouse
     * @return quantity of orders, which has concrete
     * {@code status}, {@code idUser}, {@code idWarehouse}
     */
    private int countByStatusUniversal(String status, Integer idUser, Integer idWarehouse) {
        Query query = null;
        if (idUser != 0 && idWarehouse != 0) {
            query = getEntityManager().createNamedQuery("Orders.countByStatusAndUserAndWarehouse");
            query.setParameter("orderStatus", status);
            query.setParameter("idUser", usersFacade.find(idUser));
            query.setParameter("idWarehouse", warehouseFacade.find(idWarehouse));
        } else if (idUser != 0) {
            query = getEntityManager().createNamedQuery("Orders.countByStatusAndUser");
            query.setParameter("orderStatus", status);
            query.setParameter("idUser", usersFacade.find(idUser));
        } else if (idWarehouse != 0) {
            query = getEntityManager().createNamedQuery("Orders.countByStatusAndWarehouse");
            query.setParameter("orderStatus", status);
            query.setParameter("idWarehouse", warehouseFacade.find(idWarehouse));
        } else {
            query = getEntityManager().createNamedQuery("Orders.countByStatus");
            query.setParameter("orderStatus", status);
        }
        Long qua = (Long) query.getSingleResult();
        return qua.intValue();
    }

    /**
     * Ivchenko Oleg This method is using for filling report table on page
     * gen_report_on_SOs.jsp
     *
     * @param idUser - concrete idUser
     * @param idWarehouse - concrete idWarehouse
     * @return content of <b>2nd</b> column in the table
     */
    @Override
    public List<Integer> getListQuantitiesUniversal(Integer idUser, Integer idWarehouse) {
        List<Integer> quantities = new ArrayList<Integer>();
        for (String status : statuses) {
            quantities.add(countByStatusUniversal(status, idUser, idWarehouse));
        }
        if (!quantities.isEmpty()) {
            return quantities;
        } else {
            return null;
        }
    }

    /**
     * Ivchenko Oleg This method is using for filling report table on page
     * gen_report_on_SOs.jsp
     *
     * @param idUser - concrete idUser
     * @param idWarehouse - concrete idWarehouse
     * @return content of <b>3nd</b> column in the table
     */
    @Override
    public List<BigDecimal> getListPricesUniversal(Integer idUser, Integer idWarehouse) {
        List<BigDecimal> prices = new ArrayList<BigDecimal>();
        for (String status : statuses) {
            List<Orders> orders = findByStatusUniversal(status, idUser, idWarehouse);
            BigDecimal price = new BigDecimal(0);
            for (Orders order : orders) {
                price = price.add(order.getProductPrice()).add(order.getShippingPrice());
            }
            prices.add(price);
        }
        if (!prices.isEmpty()) {
            return prices;
        } else {
            return null;
        }
    }

    //Ivchenko Oleg
    @PostConstruct
    public void postCreate() {
        statuses = getEntityManager().createNamedQuery("Orders.getAllStatuses").getResultList();
    }

    //Svetlana Shikhutskaya
    @Override
    public Orders findByTrackingNumber(int trNumber) {
        Query query = getEntityManager().createNamedQuery("Orders.findByTrackingNumber");
        query.setParameter("trackingNumber", trNumber);
        Orders order = null;
        try {
            order = (Orders) query.getSingleResult();
        } catch (NoResultException ex) {
            Logger.getLogger(OrdersFacade.class.getName()).log(Level.SEVERE, null, ex);
        } catch (NonUniqueResultException ex) {
            Logger.getLogger(OrdersFacade.class.getName()).log(Level.SEVERE, null, ex);
        }
        return order;
    }

    //Ivchenko Oleg
    @Deprecated
    public void ordOutput(List<Orders> orders) {
        if (orders == null) {
            System.out.println("NULL!!!!");
        } else if (orders.isEmpty()) {
            System.out.println("EMPTY!!!");
        } else {
            for (Orders ord : orders) {
                System.out.println(ord.getIdOrder());
            }
        }
    }

    //Ivchenko Oleg
    @Override
    @Deprecated
    public List<Orders> findOrdersByFilters(int pageNum, String status, int idWarehouse, int idUser) {
        Connection con = null;
        ResultSet rs = null;
        CallableStatement cstmt = null;
        List<Orders> orders = null;
        try {
            con = ds.getConnection();
            cstmt = con.prepareCall("{call getOrdersByFilters(?,?,?,?,?)}");
            cstmt.setString("status", status);
            cstmt.setInt("warehouseId", idWarehouse);
            cstmt.setInt("userId", idUser);
            cstmt.setInt("page_num", pageNum);
            cstmt.registerOutParameter("p_refcur", OracleTypes.CURSOR);
            cstmt.executeUpdate();
            rs = (ResultSet) cstmt.getObject("p_refcur");
            while (rs.next()) {
                Orders order = new Orders(rs.getInt("idOrder"),
                        rs.getDate("orderDate"),
                        rs.getString("orderStatus"),
                        rs.getBigDecimal("shippingPrice"),
                        rs.getBigDecimal("productPrice"),
                        rs.getString("email"),
                        rs.getString("orderAddress"),
                        rs.getInt("trackingNumber"));
                Warehouse wh = new Warehouse();
                wh.setName(rs.getString("warehouseName"));
                order.setDeliveryDate(rs.getDate("deliveryDate"));
                order.setIdWarehouse(wh);
                orders.add(order);
            }
        } catch (SQLException ex) {
            Logger.getLogger(OrdersFacade.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (cstmt != null) {
                    cstmt.close();
                }
                if (con != null) {
                    con.close();
                }
                return orders;
            } catch (SQLException ex) {
                Logger.getLogger(CategoryFacade.class.getName()).log(Level.SEVERE, null, ex);
                return orders;
            }
        }
    }

    //Svetlana Shikhutskaya, Ivchenko Oleg
    @Override
    public List<Orders> findOrdersForReport(int page, String status, int warehouse, int user, Date startDate, Date endDate) {
        Connection con = null;
        ResultSet rs = null;
        CallableStatement cstmt = null;
        List<Orders> orders = new ArrayList<Orders>();
        try {
            con = ds.getConnection();
            cstmt = con.prepareCall("{call getOrders(?,?,?,?,?,?,?)}");
            cstmt.setDate("start_date", startDate != null ? new java.sql.Date(startDate.getTime()) : null);
            cstmt.setDate("end_date", endDate != null ? new java.sql.Date(endDate.getTime()) : null);
            cstmt.setInt("page_num", page);
            cstmt.setString("status", status);
            cstmt.setInt("warehouseId", warehouse);
            cstmt.setInt("userId", user);
            cstmt.registerOutParameter("p_refcur", OracleTypes.CURSOR);
            cstmt.executeUpdate();
            rs = (ResultSet) cstmt.getObject("p_refcur");
            orders = formOrdersList(rs);
        } catch (SQLException ex) {
            Logger.getLogger(OrdersFacade.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (cstmt != null) {
                    cstmt.close();
                }
                if (con != null) {
                    con.close();
                }
                return orders;
            } catch (SQLException ex) {
                Logger.getLogger(CategoryFacade.class.getName()).log(Level.SEVERE, null, ex);
                return orders;
            }
        }
    }

    //Ivchenko Oleg
    @Override
    public int getPagesQuantity(String status, int warehouse, int user, Date startDate, Date endDate, int ordersPerPage) {
        Connection con = null;
        int ordersQu = 0;
        CallableStatement cstmt = null;
        try {
            con = ds.getConnection();
            cstmt = con.prepareCall("{ ? = call getCountOrders(?,?,?,?,?)}");
            cstmt.registerOutParameter(1, OracleTypes.NUMBER);
            cstmt.setDate("start_date", startDate != null ? new java.sql.Date(startDate.getTime()) : null);
            cstmt.setDate("end_date", endDate != null ? new java.sql.Date(endDate.getTime()) : null);
            cstmt.setString("status", status);
            cstmt.setInt("warehouseId", warehouse);
            cstmt.setInt("userId", user);
            cstmt.executeUpdate();
            ordersQu = cstmt.getInt(1);
        } catch (SQLException ex) {
            ex.printStackTrace();
            Logger.getLogger(OrdersFacade.class.getName()).log(Level.SEVERE, "Oracle function don\'t working!", ex);
        } finally {
            try {
                if (cstmt != null) {
                    cstmt.close();
                }
                if (con != null) {
                    con.close();
                }
                if (ordersQu % ordersPerPage == 0) {
                    return ordersQu / ordersPerPage;
                } else {
                    return ordersQu / ordersPerPage + 1;
                }
            } catch (SQLException ex) {
                Logger.getLogger(CategoryFacade.class.getName()).log(Level.SEVERE, "Oracle function don\'t working!", ex);
                return -1;
            }
        }
    }

    private List<Orders> formOrdersList(ResultSet rs) throws SQLException {
        List<Orders> orders = new ArrayList<Orders>();
        while (rs.next()) {
            int idOrder = rs.getInt("idOrder");
            Date date = rs.getDate("orderDate");
            String orderStatus = rs.getString("orderStatus");
            BigDecimal shippingPrice = rs.getBigDecimal("shippingPrice");
            BigDecimal productPrice = rs.getBigDecimal("productPrice");
            String email = rs.getString("email");
            String address = rs.getString("Address");
            int trackingNumber = rs.getInt("trackingNumber");
            int idUser = rs.getInt("idUser");
            int idWarehouse = rs.getInt("idWarehouse");
            Orders order = new Orders(idOrder, date, orderStatus, shippingPrice, productPrice, email, address, trackingNumber);
            Warehouse w = warehouseFacade.find(idWarehouse);
            Users u = usersFacade.find(idUser);
            order.setDeliveryDate(rs.getDate("deliveryDate"));
            order.setIdWarehouse(w);
            order.setIdUser(u);
            orders.add(order);
        }
        return orders;
    }

    public long countOrders(Users idUser) {
        Query query = em.createNamedQuery("Orders.count");
        query.setParameter("idUser", idUser);
        long count = (Long) query.getSingleResult();
        return count;
    }
}
